Files to consider
13GB Crime (Use sample to build) LSOA_popv2 -> Geo + city LSOA(2011)_toLOSA -> Geo + city location
In the television documentary “Ross Kemp and the Armed Police” broadcast 6th September 2018 by ITV, multiple claims were made regarding violent crime in the UK.
Since drugs are illegal, people pay extra money to the dealer / criminal as a fee for security & shipping.
Weapons are illegal to own in various countries.
| Index | Topic | IAAS | PAAS |
|---|---|---|---|
| 0 | Where it runs? | Runs in any cloud computer or virtual machine or computer | Underlying architecture is already handled by |
| 1 | Who is going to Setup? | User needs to setup with packages required | Setup is already done |
| 2 | Customizability | Very modular & highly customizable | Less customizability |
| 3 | Is it easy to setup? | Setting up requires time | Does not require much time like IAAS |
| 4 | Can I interact & debug? | Run a server & work in jupyter notebook, helps run things interactively & debug each step | Debugging option is highly reduced. All the visuals generated needs to be saved before viewing |
| 5 | Examples | AWS EC2, Azure Virtual Machines | AWS Glue job, Azure Data Factory |
'\nList of installed packages\n'
SparkSession - hive
* Overall crimes in UK have a cyclic pattern with peak in mid year
* Overall crime total decreased during 2014 to 2016 comparing to rest of the years
Other Crimes initally seems to be miscategorized Violence and sexual offences is increasing over timeAnti-soical behaviour trend keeps decreasing till 2019 and suddenly increases in 2020. Probably due to outrise in coronaViolent Crime has a declining trend. Data is not available after Apr-2013Shoplifting, Burglary, Vehicle crime & Criminal damange and arson are similarViolent Crime and Violence and sexual offences are combined together, there is an upward trend. As of 2021, Crimes increased 3x times of 2013CountiesGrouping crimes types together and taking count of crimes
| CRIME_TYPE | count | CRIME_% | |
|---|---|---|---|
| 0 | Anti-social behaviour | 20211528 | 31.06 |
| 1 | Violence and sexual offences | 11411540 | 17.54 |
| 2 | Criminal damage and arson | 5343182 | 8.21 |
| 3 | Other theft | 5206259 | 8.00 |
| 4 | Burglary | 4350252 | 6.68 |
| 5 | Vehicle crime | 4170769 | 6.41 |
| 6 | Shoplifting | 3227557 | 4.96 |
| 7 | Other crime | 2565111 | 3.94 |
| 8 | Public order | 2564695 | 3.94 |
| 9 | Drugs | 1682486 | 2.59 |
| 10 | Violent crime | 1673219 | 2.57 |
| 11 | Bicycle theft | 735689 | 1.13 |
| 12 | Theft from the person | 714621 | 1.10 |
| 13 | Robbery | 696008 | 1.07 |
| 14 | Possession of weapons | 283189 | 0.44 |
| 15 | Public disorder and weapons | 242145 | 0.37 |
Claim #1. Violent Crimes are Increasing Test 1: Check if Violent Crimes is Increasing
Test 2: Check if Violent Crimes is Increasing by LSOA Area
Area name Area name FIRE_ARM_PER_HEAD= and PCT (Percentage)_Note: FIRE_ARM_PER_HEAD_RANK will be equal to FIRE_ARM_PER_HEAD_PCTRANK rank because PCT is multiplied by 100 .
Claim #2. There are more firearms incidents per head in Birmingham than anywhere else in the UK Test 1: What is the rank of Birmingham?
What are the top 20 Areas involving firearms incidents? Top 20 FIRE_ARM_PER_HEAD_RANK Based on Rank
| AREA_NAME | ||||||||||||||||||||
|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|
| FIRE_ARM_PER_HEAD_RANK | 1.000000 | 2.000000 | 3.000000 | 4.000000 | 5.000000 | 6.000000 | 7.000000 | 8.000000 | 9.000000 | 10.000000 | 11.000000 | 12.000000 | 13.000000 | 14.000000 | 15.000000 | 16.000000 | 17.000000 | 18.000000 | 19.000000 | 20.000000 |
| YEAR | ||||||||||||||||||||
| 2011 | City of London | Westminster | Camden | Islington | Exeter | Ipswich | Hammersmith and Fulham | Lincoln | Uttlesford | Kingston upon Hull | Manchester | Southampton | Brent | Worcester | Tower Hamlets | Ealing | Nottingham | Portsmouth | Lambeth | Lewisham |
| 2012 | City of London | Westminster | Camden | Islington | Hammersmith and Fulham | Ipswich | Exeter | Lincoln | Worcester | Uttlesford | Kingston upon Hull | Lambeth | Manchester | Southwark | Tower Hamlets | Brent | Liverpool | Middlesbrough | Oxford | Crawley |
| 2013 | City of London | Uttlesford | Westminster | Hammersmith and Fulham | Camden | Islington | Lincoln | Kingston upon Hull | Oxford | Middlesbrough | Exeter | Merthyr Tydfil | Nottingham | Lambeth | Southwark | Tower Hamlets | Liverpool | Ipswich | Plymouth | Hackney |
| 2014 | City of London | Uttlesford | Crawley | Westminster | Boston | Nottingham | Hillingdon | Manchester | Stevenage | Luton | Lambeth | Stoke-on-Trent | Hastings | Northampton | Newcastle upon Tyne | Liverpool | Corby | Islington | Lincoln | Brighton and Hove |
| 2015 | City of London | Uttlesford | Crawley | Nottingham | Boston | Westminster | Broxbourne | Watford | Manchester | Stevenage | Newcastle upon Tyne | Lambeth | Hastings | Corby | Lincoln | Luton | Southampton | Kensington and Chelsea | Ipswich | Mansfield |
| 2016 | City of London | Uttlesford | Stevenage | Watford | Broxbourne | Hertsmere | Dacorum | Welwyn Hatfield | Crawley | East Hertfordshire | North Hertfordshire | St Albans | Three Rivers | Westminster | Hastings | Nottingham | Windsor and Maidenhead | Manchester | Southampton | Brighton and Hove |
| 2017 | Uttlesford | City of London | Stevenage | Watford | Welwyn Hatfield | Broxbourne | Dacorum | Hertsmere | North Hertfordshire | St Albans | Three Rivers | East Hertfordshire | Westminster | Manchester | Crawley | Nottingham | Lambeth | Hastings | Shepway | Brighton and Hove |
| 2018 | Uttlesford | City of London | Stevenage | Manchester | Crawley | Camden | Watford | Shepway | Westminster | Hastings | Nottingham | Broxbourne | Welwyn Hatfield | Salford | Lincoln | Tameside | Norwich | Leicester | Dacorum | Newcastle upon Tyne |
| 2019 | City of London | Crawley | Shepway | Nottingham | Uttlesford | Hastings | Westminster | Lincoln | Brighton and Hove | Mansfield | Southampton | Corby | Great Yarmouth | Luton | Portsmouth | Derby | Norwich | Peterborough | Ipswich | Leicester |
| 2020 | City of London | Crawley | Mansfield | Southampton | Portsmouth | Norwich | Lincoln | Hastings | Blackpool | Wellingborough | Worthing | Middlesbrough | Hartlepool | Birmingham | Nottingham | Preston | Peterborough | Slough | Brighton and Hove | Liverpool |
| 2021 | Mendip | Enfield | Lewisham | Barrow-in-Furness | Winchester | North Kesteven | Christchurch | Boston | Uttlesford | Bexley | Southend-on-Sea | Dartford | Dover | Newham | Blackpool | Cotswold | Merton | Lancaster | Daventry | Tandridge |
What are the top 20 Areas involving firearms incidents? Top 20 FIRE_ARM cases Based on CRIMES_RANK
| AREA_NAME | ||||||||||||||||||||
|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|
| CRIMES_RANK | 1.000000 | 2.000000 | 3.000000 | 4.000000 | 5.000000 | 6.000000 | 7.000000 | 8.000000 | 9.000000 | 10.000000 | 11.000000 | 12.000000 | 13.000000 | 14.000000 | 15.000000 | 16.000000 | 17.000000 | 18.000000 | 19.000000 | 20.000000 |
| YEAR | ||||||||||||||||||||
| 2011 | Unknown Area Name | Birmingham | Manchester | Westminster | Liverpool | Leeds | Camden | Ealing | Brent | Leicester | Islington | Cardiff | Nottingham | Lambeth | Kingston upon Hull | Bradford | Southwark | Lewisham | Southampton | Tower Hamlets |
| 2012 | Unknown Area Name | Birmingham | Manchester | Westminster | Leeds | Liverpool | Camden | Bradford | Islington | Lambeth | Brent | Cardiff | Southwark | Ealing | Leicester | Kingston upon Hull | Tower Hamlets | Hammersmith and Fulham | Nottingham | Croydon |
| 2013 | Unknown Area Name | Birmingham | Liverpool | Manchester | Westminster | Leeds | Lambeth | Bradford | Nottingham | Southwark | Cardiff | Leicester | Camden | Tower Hamlets | Kingston upon Hull | Hillingdon | Hammersmith and Fulham | Cornwall | Sheffield | Lewisham |
| 2014 | Unknown Area Name | Birmingham | Manchester | Uttlesford | Sheffield | Liverpool | Nottingham | Leeds | Hillingdon | Westminster | Lambeth | Bradford | Leicester | Newcastle upon Tyne | Stoke-on-Trent | Brighton and Hove | Croydon | Sunderland | Southwark | Luton |
| 2015 | Unknown Area Name | Birmingham | Manchester | Nottingham | Leeds | Sheffield | Liverpool | Uttlesford | Lambeth | Bradford | Newcastle upon Tyne | Crawley | Westminster | Leicester | Hackney | Southampton | Derby | Newham | Southwark | Sunderland |
| 2016 | Unknown Area Name | Birmingham | Manchester | Leeds | Sheffield | Nottingham | Uttlesford | Westminster | Bradford | Liverpool | Dacorum | Lambeth | Stevenage | Watford | Brighton and Hove | Leicester | Newcastle upon Tyne | Broxbourne | Kirklees | East Hertfordshire |
| 2017 | Unknown Area Name | Manchester | Birmingham | Uttlesford | Leeds | Sheffield | Nottingham | Dacorum | Lambeth | Westminster | Welwyn Hatfield | Stevenage | Liverpool | Leicester | Bradford | Southwark | Croydon | Brighton and Hove | St Albans | Newham |
| 2018 | Unknown Area Name | Manchester | Birmingham | Leeds | Uttlesford | Camden | Sheffield | Nottingham | Leicester | Bradford | Liverpool | Westminster | Kirklees | Southwark | Lambeth | Newcastle upon Tyne | Brighton and Hove | Salford | Newham | Southampton |
| 2019 | Unknown Area Name | Birmingham | Leeds | Manchester | Sheffield | Nottingham | Liverpool | Bradford | Leicester | Westminster | Kirklees | Brighton and Hove | Doncaster | Newcastle upon Tyne | Lambeth | Southampton | Derby | Croydon | Newham | Southwark |
| 2020 | Unknown Area Name | Birmingham | Leeds | Sheffield | Liverpool | Bradford | Nottingham | Leicester | Southampton | Doncaster | Brighton and Hove | Coventry | Kirklees | Newcastle upon Tyne | Croydon | Portsmouth | Wolverhampton | Plymouth | Derby | Lambeth |
| 2021 | Unknown Area Name | Birmingham | Leeds | Sheffield | Nottingham | Bradford | Liverpool | Leicester | Doncaster | Southampton | Derby | Kirklees | Sandwell | Lambeth | Bristol | Southwark | Newcastle upon Tyne | Portsmouth | Northampton | Brighton and Hove |
Trend of Weapons & Drug related crimes
Overall Correlation Check between Weapons & Drug crimes Checking If there is a correlation between Weapons & Drugs Involvement
| DRUG_CRIME_COUNT | WEAPON_CRIME_COUNT | |
|---|---|---|
| DRUG_CRIME_COUNT | 1.000000 | 0.541564 |
| WEAPON_CRIME_COUNT | 0.541564 | 1.000000 |
Trend by Area Name | Weapons & Drug related crimes
Trend of Weapons & Drug related crimes by LSOA Area
Correlation for Area Name: Birmingham
| count Drugs | count WEAPON_CRIME | |
|---|---|---|
| count Drugs | 1.000000 | 0.745904 |
| count WEAPON_CRIME | 0.745904 | 1.000000 |
Correlation for Area Name: East Riding of Yorkshire
| count Drugs | count WEAPON_CRIME | |
|---|---|---|
| count Drugs | 1.000000 | 0.723121 |
| count WEAPON_CRIME | 0.723121 | 1.000000 |
Correlation for Area Name: Manchester
| count Drugs | count WEAPON_CRIME | |
|---|---|---|
| count Drugs | 1.000000 | 0.733751 |
| count WEAPON_CRIME | 0.733751 | 1.000000 |
Correlation for Area Name: Salford
| count Drugs | count WEAPON_CRIME | |
|---|---|---|
| count Drugs | 1.000000 | 0.708419 |
| count WEAPON_CRIME | 0.708419 | 1.000000 |
Correlation for Area Name: Stockport
| count Drugs | count WEAPON_CRIME | |
|---|---|---|
| count Drugs | 1.000000 | 0.701101 |
| count WEAPON_CRIME | 0.701101 | 1.000000 |
1. Leeds, Birmingham, Sheffield, Bradford, Liverpool, Bristol, Manchester are top areas where the Burglary crimes trends are high compared to other areas
2. We can create buckets / bins based on the ranks / #of crimes
1. Using binning technique, 10 Burglary crimes can be bucketed into 10 groups based on #of Burglary crimes across years. Looking at the #of areas in the bucket, after 4th bucket we can combine everything into 1 group
A better technique which can be used is K-Means Segmentation.
What are the top 20 Areas involving Burglary incidents? Top 20 Burglary cases Based on BURGLARY_CRIMES_RANK
| AREA_NAME | ||||||||||||||||||||
|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|
| BURGLARY_CRIMES_RANK | 1 | 2 | 3 | 4 | 5 | 6 | 7 | 8 | 9 | 10 | 11 | 12 | 13 | 14 | 15 | 16 | 17 | 18 | 19 | 20 |
| YEAR | ||||||||||||||||||||
| 2010 | Leeds | Birmingham | Bradford | Manchester | Liverpool | Bristol | Kirklees | Redbridge | Barnet | Croydon | Sheffield | Enfield | Lewisham | Waltham Forest | Haringey | Brent | Sandwell | Newham | Coventry | Ealing |
| 2011 | Leeds | Birmingham | Bradford | Manchester | Sheffield | Kirklees | Liverpool | Bristol | Barnet | Croydon | Doncaster | Kingston upon Hull | Unknown Area Name | Nottingham | Wakefield | Ealing | Brent | Lambeth | Redbridge | Hillingdon |
| 2012 | Unknown Area Name | Leeds | Birmingham | Bradford | Manchester | Sheffield | Liverpool | Barnet | Bristol | Doncaster | Kirklees | Westminster | Croydon | Ealing | Coventry | Brent | Cardiff | Enfield | Kingston upon Hull | Lambeth |
| 2013 | Unknown Area Name | Leeds | Birmingham | Manchester | Bradford | Sheffield | Liverpool | Barnet | Kingston upon Hull | Kirklees | Croydon | Westminster | Bristol | Doncaster | Lambeth | Ealing | Southwark | Enfield | Coventry | Lewisham |
| 2014 | Unknown Area Name | Leeds | Birmingham | Manchester | Bradford | Sheffield | Liverpool | Bristol | Kingston upon Hull | Doncaster | Barnet | Kirklees | Nottingham | Leicester | Westminster | Croydon | Cardiff | Enfield | Lambeth | Coventry |
| 2015 | Unknown Area Name | Leeds | Birmingham | Manchester | Bradford | Liverpool | Sheffield | Bristol | Kirklees | Barnet | Kingston upon Hull | Westminster | County Durham | Leicester | Southwark | Doncaster | Coventry | Nottingham | Cardiff | Rochdale |
| 2016 | Unknown Area Name | Birmingham | Leeds | Manchester | Bradford | Liverpool | Bristol | Kirklees | Sheffield | Kingston upon Hull | Barnet | Westminster | Leicester | County Durham | Lambeth | Stockport | Wigan | Southwark | Southampton | Bolton |
| 2017 | Unknown Area Name | Birmingham | Leeds | Manchester | Bradford | Liverpool | Sheffield | Bristol | County Durham | Doncaster | Kirklees | Leicester | Bolton | Stockport | Wigan | Barnet | Westminster | Coventry | Kingston upon Hull | Southwark |
| 2018 | Unknown Area Name | Birmingham | Leeds | Manchester | Bradford | Sheffield | Liverpool | Westminster | Kirklees | Leicester | Bristol | Barnet | Sandwell | Doncaster | Camden | Coventry | County Durham | Brent | Tower Hamlets | Hackney |
| 2019 | Unknown Area Name | Birmingham | Leeds | Bradford | Sheffield | Westminster | Doncaster | Liverpool | Kirklees | Barnet | County Durham | Manchester | Camden | Hackney | Southwark | Enfield | Lambeth | Tower Hamlets | Kingston upon Hull | Sandwell |
| 2020 | Unknown Area Name | Birmingham | Leeds | Bradford | Sheffield | Liverpool | Doncaster | Westminster | Barnet | Tower Hamlets | Bristol | County Durham | Lambeth | Hackney | Southwark | Kingston upon Hull | Enfield | Lewisham | Croydon | Sandwell |
| 2021 | Unknown Area Name | Birmingham | Leeds | Sheffield | Liverpool | Bradford | Doncaster | Hackney | Wandsworth | Barnet | Bristol | Tower Hamlets | Lewisham | Lambeth | Southwark | Enfield | Westminster | Ealing | Haringey | Croydon |
What are the top 20 Areas involving Burglary incidents Across Years ? Top 20 Burglary cases Based on OVERALL_BURGLARY_CRIMES_RANK
| AREA_NAME | |
|---|---|
| OVERALL_BURGLARY_CRIMES_RANK | |
| 1 | Unknown Area Name |
| 2 | Birmingham |
| 3 | Leeds |
| 4 | Bradford |
| 5 | Manchester |
| 6 | Sheffield |
| 7 | Liverpool |
| 8 | Bristol |
| 9 | Kirklees |
| 10 | Barnet |
| 11 | Doncaster |
| 12 | Westminster |
| 13 | Kingston upon Hull |
| 14 | Croydon |
| 15 | Leicester |
| 16 | Lambeth |
| 17 | Coventry |
| 18 | Southwark |
| 19 | County Durham |
| 20 | Enfield |
| number_of_areas | min_number_of_crime | max_number_of_crime | |
|---|---|---|---|
| Crime_Bins | |||
| Crime_Bin_1 | 242 | 51.0 | 12416.0 |
| Crime_Bin_2 | 67 | 12606.0 | 24812.0 |
| Crime_Bin_3 | 28 | 25372.0 | 35979.0 |
| Crime_Bin_4 | 5 | 37566.0 | 42214.0 |
| Crime_Bin_5 | 2 | 50847.0 | 57860.0 |
| Crime_Bin_6 | 2 | 65054.0 | 68604.0 |
| Crime_Bin_7 | 0 | NaN | NaN |
| Crime_Bin_8 | 1 | 98979.0 | 98979.0 |
| Crime_Bin_9 | 1 | 102702.0 | 102702.0 |
| Crime_Bin_10 | 1 | 123986.0 | 123986.0 |
Income estimates for small areas, England and Wales: financial year ending 2018
Claim #2. Burglaries are increasing, decreasing, or are stable Test 1: Check if Burglary Crimes is Increasing Burglary crime count mean value: 34525.80952380953
Test 2: Check if "Burglaries are increasing, decreasing, or are stable" by LSOA Area
MSE: 3376915.47 RMSE: 1837.64 R-squared: -0.30
'\n\nStopping Services / Memory Clean\n\n'
Violent Crime is increasing - Media:
There are more firearms incidents per head in Birmingham than anywhere else in the UK - Media:
Conclusion:
We can draw 2 conclusions here,
Based on #of Firearm incidents reported, Birmingham is the 2nd highest reported</trong> & 1st position is crimes reported in unknown location
Firearms incident reports per person in Birmingham is low (Ranking around 100). Hence "More firearms incidents per head in Birmingham" claim is False
Crimes involving firearms are closely associated with drugs offences - Media
"Crime Detection and Prediction Using Big Data Analytics: A Case Study of Chicago" by Dong Wang, et al., published in IEEE Transactions on Big Data in 2018, https://www.jetir.org/papers/JETIR2107201.pdf
"Using Machine Learning to Assist Crime Prevention," 2017 6th IIAI International Congress on Advanced Applied Informatics (IIAI-AAI), Hamamatsu, Japan, 2017, pp. 1029-1030, doi: 10.1109/IIAI-AAI.2017.46.
Office for National Statistics (ONS), published 5 March 2020, ONS website, statistical bulletin, Income estimates for small areas, England and Wales: financial year ending 2018